/* QAD_CumulativeOrderCostReport.p - Cumulative Order Cost Report             */
/* Copyright 1986-2011 QAD Inc., Santa Barbara, CA, USA.                      */
/* All rights reserved worldwide.  This is an unpublished work.               */
/* $Id:: QAD_CumulativeOrderCostReport.p 9927 2011-09-02 08:19:58Z jxx     $: */
/*                                                                            */
/* Report matches legacy reworp02.p for use by QAD Reporting Framework.       */
/*                                                                            */

{mfsubdirs.i}
{{&US_BBI}mfdeclre.i}
{{&US_BBI}gplabel.i}
{{&US_PX}pxmaint.i}
{{&US_PX}pxphdef.i wowrxr}
{{&US_WO}woconsdf.i}
{{&US_WR}wrttiro.i reference-only}

{com/qad/shell/report/dsReportRequest.i}
{com/qad/shell/report/ReportConstants.i}

/*============================================================================*/
/* ****************************  TEMP TABLES  ******************************* */
/*============================================================================*/

/* Temp-table definiation block */

define temp-table tt_wo_mstr no-undo like wo_mstr
   field si_desc        like si_mstr.si_desc
   field pt_desc1       like pt_mstr.pt_desc1
   field ptal_alt_part  like ptal_mstr.ptal_alt_part
   field mfg_type       as character
   field ln_desc        like ln_mstr.ln_desc
   field wostatus       as character
   field unique_id      as integer
   index idx unique_id
.

define temp-table ttComments no-undo
   field cmmt           as character
   field ref_unique_id  like tt_wo_mstr.unique_id
.

define temp-table tt_wr_route no-undo like wr_route
   field wc_desc        like wc_mstr.wc_desc
   field iro_mtl_ll     like iro_det.iro_mtl_ll
   field iro_mtl_tl     like iro_det.iro_mtl_tl
   field iro_lbr_ll     like iro_det.iro_lbr_ll
   field iro_lbr_tl     like iro_det.iro_lbr_tl
   field iro_bdn_ll     like iro_det.iro_bdn_ll
   field iro_bdn_tl     like iro_det.iro_bdn_tl
   field iro_sub_ll     like iro_det.iro_sub_ll
   field iro_sub_tl     like iro_det.iro_sub_tl
   field iro_cost_tot   like iro_det.iro_cost_tot
   field unique_id      as integer
   field ref_unique_id  like tt_wo_mstr.unique_id
   index idx unique_id
.

define temp-table ttReportBom no-undo
   field wod_part       like wod_det.wod_part
   field ptal_alt_part  like ptal_mstr.ptal_alt_part
   field wod_bom_qty    like wod_det.wod_bom_qty
   field wod_bom_amt    like wod_det.wod_bom_amt
   field ext_amt        like iro_det.iro_cost_tot
   field ref_unique_id  like tt_wr_route.unique_id
.

define temp-table ttReportOptions no-undo
   field report_bom  like mfc_ctrl.mfc_logical
   field ordertype   as character
.

define dataset dsReportResults for tt_wo_mstr,ttComments,
                  tt_wr_route,ttReportBom,ttReportOptions.

/*============================================================================*/
/* *****************************  MAIN BLOCK  ******************************* */
/*============================================================================*/
define input  parameter runReport    as  logical no-undo.
define input  parameter reportHandle as  handle no-undo.
define input  parameter dataset      for dsReportRequest.
define output parameter dataset-handle phReportResults.

{com/qad/shell/report/reporting.i}
{com/qad/shell/report/reports/QAD_HelperFunctions.i}

define variable vhDS             as handle no-undo.
define variable order_type_key   like lngd_key1 initial "0" no-undo.
define variable c-lngd-dataset   like lngd_det.lngd_dataset no-undo
                                 initial "wo_mstr".
define variable orderType        as character no-undo.

empty temp-table tt_wo_mstr no-error.
empty temp-table ttComments no-error.
empty temp-table tt_wr_route no-error.
empty temp-table ttReportBom no-error.
empty temp-table ttReportOptions no-error.


for first ttReportRequest no-lock:

   vhDS = dataset dsReportResults:handle.
   run FillMetaData in reportHandle(
      vhDS, "QAD_CumulativeOrderCostReport.meta.xml").

   run SetReportMetaData (input reportHandle).

   if runReport then do:
      run RunReport
         (output dataset-handle phReportResults).
   end.

end.

PROCEDURE SetReportMetaData:

   define input parameter  ipReportHandle as handle.

   run SetFieldMetaParameter in ipReportHandle ("ttReportOptions",
      "report_bom", "DataFormat", getLabel("YES/NO")).

   run SetFieldMetaParameter in ipReportHandle("tt_wo_mstr",
         "wo_site", "DefaultValue", global_site).

   for first lngd_det where lngd_dataset = c-lngd-dataset
      and lngd_field   = "orderType"
      and lngd_lang    = global_user_lang
      and lngd_key1    = order_type_key
   no-lock:
   end. /* for first lngd_det */

   if available lngd_det
   then do:
      orderType = lngd_key2.
   end. /* if available lngd_det */
   else do:
      orderType = "".
   end. /* else do */

   run SetFieldMetaParameter in ipReportHandle(
      "ttReportOptions", "ordertype", "DefaultValue", orderType).

END PROCEDURE. /* PROCEDURE ModifyMetaData */

/* Data retrieving logic block */
PROCEDURE RunReport:

   define output parameter dataset-handle phReportResults.

   /* Retrieve the data from database */
   define variable queryString      as   character no-undo.
   define variable hMainQuery       as   handle    no-undo.
   define query    MainQuery        for wo_mstr, pt_mstr, pl_mstr.
   define buffer   womstr           for wo_mstr.
   define variable unique_id        as integer no-undo.
   define variable unique_route_id  as integer no-undo.
   define variable report_bom       like mfc_ctrl.mfc_logical no-undo.
   define variable wostatus         as character no-undo.
   define variable i                as integer no-undo.
   define variable mfg_type         as character no-undo.
   define variable ext_amt          like iro_cost_tot no-undo.

   assign
      hMainQuery           =  query MainQuery:handle
      report_bom           =  IsTrueValue("ttReportOptions", "report_bom", true)
      orderType            =  GetFilterValue( "ttReportOptions",
                                              "ordertype",
                                              orderType)
   .

   /* CLEAR  TEMP TABLE OF OPERATION COSTS */
   {{&US_PX}pxrun.i
      &PROC       = 'initialize_tt_iro_det'
      &PROGRAM    = 'wowrxr.p'
      &HANDLE     = ph_wowrxr
      &NOAPPERROR = true
   }

   for first lngd_det where
      lngd_dataset      = c-lngd-dataset
      and lngd_field    = "orderType"
      and lngd_lang     = global_user_lang
      and lngd_key2     = orderType
   no-lock:
   end. /* for first lngd_det */

   if not available lngd_det
   then do:
      /* MUST BE (S)cheduled or (D)iscrete or (B)oth */
      {{&US_BBI}pxmsg.i &MSGNUM=10346 &ERRORLEVEL=4}
      return.
   end. /* if not available lngd_det */

   order_type_key = lngd_key1.

   queryString = "for each wo_mstr no-lock where wo_domain = "
                  + quoter(global_domain).

   run AddAllConditions in reportHandle
      ("wo_mstr", input-output queryString).

   if not IsConditionEmpty("ttReportBom", "ptal_alt_part")
   then do:
      queryString = queryString + " , for first ptal_mstr no-lock "
                     + " where ptal_mstr.ptal_domain = "
                     + quoter(global_domain)
                     + " and ptal_mstr.ptal_part = op_hist.op_part "
                     + " and ptal_mstr.ptal_active ".

      run AddAllConditions in reportHandle
      ( " ptal_mstr " , input-output hMainQuery ).
   end. /* if not IsConditionEmpty(...) */

   queryString = queryString + " and wo_type = 'c' "
                  + " and ((wo_mfg_type = " + quoter({&MFG_TYPE_DISCRETE})
                  + " and ( " + quoter(order_type_key) + " = "
                  + quoter({&MFG_TYPE_DISCRETE}) + " or "
                  + quoter(order_type_key) + " = "
                  + quoter({&MFG_TYPE_DISCRETE_AND_REPETITIVE}) + " )) "
                  + " or (wo_mfg_type = " + quoter({&MFG_TYPE_REPETITIVE})
                  + " and ( " + quoter(order_type_key)
                  + " = " + quoter({&MFG_TYPE_REPETITIVE})
                  + " or " + quoter(order_type_key) + " = "
                  + quoter({&MFG_TYPE_DISCRETE_AND_REPETITIVE}) + " ))) "
                  + " use-index wo_type_part, "
                  + " each pt_mstr no-lock where pt_mstr.pt_domain = "
                  + quoter(global_domain) + " and pt_part = wo_part,"
                  + " each pl_mstr no-lock where pl_mstr.pl_domain = "
                  + quoter(global_domain)
                  + " and pl_prod_line = pt_prod_line "
                  + " by wo_type by wo_site by wo_part "
                  + " by wo_line by wo_due_date by wo_lot: ".

   hMainQuery:query-prepare(queryString).
   hMainQuery:query-open().
   hMainQuery:get-next().

   repeat while not hMainQuery:query-off-end:

      for first womstr where womstr.wo_domain = global_domain
         and womstr.wo_lot = wo_mstr.wo_lot
      no-lock:
      end. /* for first womstr */

      for first si_mstr where si_mstr.si_domain = global_domain
         and si_site = womstr.wo_site
      no-lock:
      end. /* for first si_mstr */

      for first pt_mstr where pt_mstr.pt_domain = global_domain
         and pt_part = womstr.wo_part
      no-lock:
      end. /* for first pt_mstr */

      for first ln_mstr where ln_mstr.ln_domain = global_domain
         and ln_site = womstr.wo_site
         and ln_line = womstr.wo_line
      no-lock:
      end. /* for first ln_mstr */

      for first ptal_mstr where ptal_mstr.ptal_domain = global_domain
         and ptal_part = womstr.wo_part
         and ptal_active
      no-lock:
      end. /* for first ptal_mstr */

      if womstr.wo_status = "C"
      then do:
         wostatus = getTermLabel("CLOSED",20).
      end. /* if womstr.wo_status */
      else do:
         if womstr.wo_due_date < today
         then do:
            wostatus = getTermLabel("EXPIRED",20).
         end. /* if womstr */
         else do:
            wostatus = getTermLabel("ACTIVE",20).
         end. /* else do */
      end. /* else do */

      for first lngd_det where lngd_dataset = "wo_mstr"
         and lngd_field = "orderType"
         and lngd_lang  = global_user_lang
         and lngd_key1  = womstr.wo_mfg_type
      no-lock:
         mfg_type = lngd_key2.
      end. /* for first lngd_det */

      unique_id = unique_id + 1.
      create tt_wo_mstr.
      buffer-copy womstr to tt_wo_mstr no-error.
      assign
         tt_wo_mstr.si_desc         = si_mstr.si_desc when (available si_mstr)
         tt_wo_mstr.pt_desc1        = pt_mstr.pt_desc1 when (available pt_mstr)
         tt_wo_mstr.ptal_alt_part   = ptal_mstr.ptal_alt_part
                                      when available(ptal_mstr)
         tt_wo_mstr.mfg_type        = mfg_type
         tt_wo_mstr.ln_desc         = ln_mstr.ln_desc when (available ln_mstr)
         tt_wo_mstr.wostatus        = wostatus
         tt_wo_mstr.unique_id       = unique_id
      .

      for each cmt_det no-lock
         where cmt_det.cmt_domain = global_domain
         and cmt_indx = wo_cmtindx
         and cmt_indx > 0:
         do i = 1 to 15:
            if cmt_cmmt[i] <> ""
            then do:
               create ttComments.
               assign
                  ttComments.cmmt            = cmt_cmmt[i]
                  ttComments.ref_unique_id   = unique_id
               .
            end. /* if cmt_cmmt */
         end. /* do i */
      end. /* for each cmt_det */

      for each wr_route no-lock
            where wr_route.wr_domain = global_domain
            and wr_lot = wo_mstr.wo_lot:
         /* RETRIEVE TEMP TABLE OF OPERATION COSTS */
         {{&US_PX}pxrun.i &PROC = 'getRoutingCosts'
            &PROGRAM    = 'wowrxr.p'
            &HANDLE     = ph_wowrxr
            &PARAM      = "(input wo_lot,
              input {&IRO_COST_SOURCE_WO_STD},
              output table tt_iro_det bind)"
            &NOAPPERROR = true
         }

         for first tt_iro_det
            where tt_iro_det.tt_iro_costSource = {&IRO_COST_SOURCE_WO_STD}
            and tt_iro_det.tt_iro_lot  = wr_lot
            and tt_iro_det.tt_iro_op   = wr_op
         no-lock:
         end. /* for first tt_iro_det */

         for first wc_mstr where wc_mstr.wc_domain = global_domain
            and wc_wkctr = wr_wkctr and wc_mch = wr_mch
         no-lock:
         end. /* for first wc_mstr */

         unique_route_id = unique_route_id + 1.
         create tt_wr_route.
         buffer-copy wr_route to tt_wr_route no-error.
         assign
            tt_wr_route.wc_desc        = wc_mstr.wc_desc
                                          when (available wc_mstr)
            tt_wr_route.unique_id      = unique_route_id
            tt_wr_route.ref_unique_id  = unique_id
         .

         if available tt_iro_det
         then do:
            assign
               tt_wr_route.iro_mtl_ll     = tt_iro_det.tt_iro_mtl_ll
               tt_wr_route.iro_mtl_tl     = tt_iro_det.tt_iro_mtl_tl
               tt_wr_route.iro_lbr_ll     = tt_iro_det.tt_iro_lbr_ll
               tt_wr_route.iro_lbr_tl     = tt_iro_det.tt_iro_lbr_tl
               tt_wr_route.iro_bdn_ll     = tt_iro_det.tt_iro_bdn_ll
               tt_wr_route.iro_bdn_tl     = tt_iro_det.tt_iro_bdn_tl
               tt_wr_route.iro_sub_ll     = tt_iro_det.tt_iro_sub_ll
               tt_wr_route.iro_sub_tl     = tt_iro_det.tt_iro_sub_tl
               tt_wr_route.iro_cost_tot   = tt_iro_det.tt_iro_cost_tot
            .
         end. /* if available tt_iro_det */

         if report_bom then do:

            for each wod_det no-lock
                  where wod_det.wod_domain = global_domain
                  and wod_lot    = wr_route.wr_lot
                  and wod_op     = wr_route.wr_op,
                  each qad_wkfl no-lock
                  where qad_wkfl.qad_domain = global_domain
                  and qad_key1   = "MFWORLA"
                  and qad_key2   = wod_lot + wod_part + string(wod_op)
                  and qad_charfld[1] = "s" by wod_part:

               ext_amt = wod_bom_qty * wod_bom_amt.

               for first ptal_mstr where ptal_mstr.ptal_domain = global_domain
                  and ptal_part = wod_part
                  and ptal_active
               no-lock:
               end.

               create ttReportBom.
               assign
                  ttReportBom.wod_part       = wod_det.wod_part
                  ttReportBom.ptal_alt_part  = ptal_mstr.ptal_alt_part
                                                when available(ptal_mstr)
                  ttReportBom.wod_bom_qty    = wod_det.wod_bom_qty
                  ttReportBom.wod_bom_amt    = wod_det.wod_bom_amt
                  ttReportBom.ext_amt        = ext_amt
                  ttReportBom.ref_unique_id  = unique_route_id
               .
            end. /* for each wod_det */
         end. /* if report_bom */
      end. /* for each wr_route */
      hMainQuery:get-next().
   end. /* Repeat query */

   hMainQuery:query-close().
   delete object hMainQuery.

   phReportResults = dataset dsReportResults:handle.

END PROCEDURE.

